Imports System.Data.SqlClient
Public Class Database
    Private strsql As String = Decode(System.Configuration.ConfigurationSettings.AppSettings("ConnectionString"))
    Private cardstr As String = Decode(System.Configuration.ConfigurationSettings.AppSettings("CardConnStr"))
    Private SqlConn As System.Data.SqlClient.SqlConnection
    Private sqladapter As System.Data.SqlClient.SqlDataAdapter
    Private sqlcmd As System.Data.SqlClient.SqlCommand
    Public iscardServer As Boolean = False

    Private Sub SQLconnect()


        ' Try

        SqlConn = New SqlClient.SqlConnection

        SqlConn.ConnectionString = IIf(iscardServer, cardstr, strsql)
        SqlConn.Open()

        'Catch ex As Exception
        'Throw New Exception(ex.Message)
        'End Try


    End Sub
    Private Sub SQLClose()
        If Not SqlConn.State = ConnectionState.Closed Then
            SqlConn.Close()
        End If

    End Sub
    Public Sub update(ByVal sql As String)
        SQLconnect()
        sqlcmd = New SqlClient.SqlCommand(sql, SqlConn)
        sqlcmd.ExecuteNonQuery()
        SQLClose()

    End Sub
    Public Sub updateTable(ByVal dt As DataTable, ByVal sql As String)
        SQLconnect()
        Dim da As SqlDataAdapter = New SqlDataAdapter(sql, SqlConn)
        Dim cb As SqlCommandBuilder = New SqlCommandBuilder(da)
        da.Update(dt)
        

        'adapter.SelectCommand = New SqlClient.SqlCommand(sql, SqlConn)
        'adapter.Update(dt)
        SQLClose()
    End Sub

    Public Function getvalue(ByVal sql As String) As DataSet
        Dim dataset As New DataSet
        Dim adapter As New SqlClient.SqlDataAdapter
        SQLconnect()
        adapter.SelectCommand = New SqlClient.SqlCommand(sql, SqlConn)
        adapter.Fill(dataset)
        SQLClose()
        Return dataset
    End Function
    Public Function getStoredProcedure(ByVal name As String) As DataSet
        Dim ds As New DataSet
        SQLconnect()
        Dim adapter As New SqlClient.SqlDataAdapter(name, SqlConn)
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure
        adapter.Fill(ds)
        SQLClose()
        Return ds
    End Function
    Public Function getStoredProcedure(ByVal name As String, ByVal parameter As SqlClient.SqlParameterCollection) As DataSet
        Dim ds As New DataSet
        SQLconnect()
        Dim adapter As New SqlClient.SqlDataAdapter(name, SqlConn)
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure
        For i As Byte = 1 To parameter.Count
            adapter.SelectCommand.Parameters.Add(parameter.Item(i))
        Next

        adapter.Fill(ds)
        SQLClose()
        Return ds
    End Function
    Public Function getReport(ByVal StartDate As Date, ByVal EndDate As Date, ByVal limit As Double) As DataSet
        Dim ds As New DataSet
        SQLconnect()
        Dim adapter As New SqlClient.SqlDataAdapter("RCoupon", SqlConn)
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@StartDate", SqlDbType.DateTime, 50)).Value = StartDate
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EndDate", SqlDbType.DateTime)).Value = EndDate
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@CouponLimit", SqlDbType.Int)).Value = CInt(limit)

        adapter.Fill(ds)
        SQLClose()
        Return ds
    End Function
    Public Function getDetailReport(ByVal StartDate As Date, ByVal EndDate As Date, ByVal EmployeeID As String) As DataSet
        Dim ds As New DataSet
        SQLconnect()
        Dim adapter As New SqlClient.SqlDataAdapter("TransactionsOfID", SqlConn)
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@StartDate", SqlDbType.DateTime, 50)).Value = StartDate
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EndDate", SqlDbType.DateTime)).Value = EndDate
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@ID", SqlDbType.VarChar, 8)).Value = EmployeeID

        adapter.Fill(ds)
        SQLClose()
        Return ds
    End Function
    Public Sub InsertImageToEmployee(ByVal data As Byte(), ByVal EmployeeID As String)
        SQLconnect()
        Dim sql As String = "update  Employee_CuChi  set  ePicture=@ePicture where EmployeeID=@EmployeeID"
        Dim adapter As New SqlClient.SqlDataAdapter(sql, SqlConn)
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@EmployeeID", SqlDbType.NVarChar, 50)).Value = EmployeeID
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@ePicture", SqlDbType.Image)).Value = Data

        adapter.SelectCommand.ExecuteNonQuery()
        SQLClose()
    End Sub
    Public Sub InsertImageToDish(ByVal data As Byte(), ByVal DishID As String)
        SQLconnect()
        Dim sql As String = "update Dish_CuChi  set  DishImage=@ePicture where DishID=@DishID"
        Dim adapter As New SqlClient.SqlDataAdapter(sql, SqlConn)
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@DishID", SqlDbType.Int, 4)).Value = DishID
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@ePicture", SqlDbType.Image)).Value = data

        adapter.SelectCommand.ExecuteNonQuery()
        SQLClose()
    End Sub
    Public Sub SetLastCard(ByVal data As Byte())
        SQLconnect()
        Dim sql As String = "update EV_LOG set EVENT_GUID = 'x' where RID = @RID"
        Dim adapter As New SqlClient.SqlDataAdapter(sql, SqlConn)
        adapter.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@RID", SqlDbType.VarBinary, 18)).Value = data
        adapter.SelectCommand.ExecuteNonQuery()
        SQLClose()
    End Sub
    
    
    Public Function getvalue(ByVal sql As String, ByVal colname As String) As String
        Dim dataset As New DataSet
        Dim adapter As New SqlClient.SqlDataAdapter
        SQLconnect()
        adapter.SelectCommand = New SqlClient.SqlCommand(sql, SqlConn)
        adapter.Fill(dataset)
        SQLClose()
        If dataset.Tables(0).Rows.Count > 0 Then
            If IsDBNull(dataset.Tables(0).Rows(0).Item(colname)) Then
                Return ""
            Else
                Return dataset.Tables(0).Rows(0).Item(colname)
            End If
        Else
            Return ""
        End If

    End Function

    Public Sub sqlquerySelect(ByVal sql As String)


        sqlcmd = New SqlClient.SqlCommand
        sqladapter = New SqlClient.SqlDataAdapter
        SQLconnect()
        sqlcmd.Connection() = SqlConn
        sqlcmd.CommandText = sql
        sqladapter.SelectCommand = sqlcmd
        Dim myReader As SqlClient.SqlDataReader = sqlcmd.ExecuteReader

        While myReader.Read()
            Console.WriteLine(myReader.GetValue(0))
        End While
        myReader.Close()
        SQLClose()
    End Sub
End Class
